SQL All-in-One For Dummies by Taylor Allen G

Author:Taylor, Allen G.
Language: eng
Format: epub
Publisher: John Wiley & Sons, Inc.
Published: 2011-03-03T16:00:00+00:00

You can’t fetch rows from a cursor until you open the cursor. When you open a cursor, the values of variables referenced in the DECLARE CURSOR statement become fixed, as do all current datetime functions. Consider the following example of SQL statements embedded in a host language program:

EXEC SQL DECLARE CURSOR C1 FOR SELECT * FROM ORDERS

WHERE ORDERS.Customer = :NAME

AND DueDate < CURRENT_DATE ;

NAME := ‘Acme Co’; //A host language statement

EXEC SQL OPEN C1;

NAME := ‘Omega Inc.’; //Another host statement

...

EXEC SQL UPDATE ORDERS SET DueDate = CURRENT_DATE;

The OPEN statement fixes the value of all variables referenced in the DECLARE CURSOR statement and also fixes a value for all current datetime functions. Thus the second assignment to the name variable (NAME := ‘Omega Inc.’) has no effect on the rows that the cursor fetches. (That value of NAME is used the next time you open C1.) And even if the OPEN statement is executed a minute before midnight and the UPDATE statement is executed a minute after midnight, the value of CURRENT_DATE in the UPDATE statement is the value of that function at the time the OPEN statement executed. This is true even if DECLARE CURSOR doesn’t reference the date-time function.

Operating on a Single Row

Whereas the DECLARE CURSOR statement specifies the cursor’s name and scope, and the OPEN statement collects the table rows selected by the DECLARE CURSOR query expression, the FETCH statement actually retrieves the data. The cursor may point to one of the rows in the cursor’s scope, or to the location immediately before the first row in the scope, or to the location immediately after the last row in the scope, or to the empty space between two rows. You can specify where the cursor points with the orientation clause in the FETCH statement.

FETCH syntax

The syntax for the FETCH statement is

FETCH [[orientation] FROM] cursor-name

INTO target-specification [, target-specification]... ;

Seven orientation options are available:

♦ NEXT

♦ PRIOR

♦ FIRST

♦ LAST

♦ ABSOLUTE

♦ RELATIVE

♦ <simple value specification>

The default option is NEXT, which was the only orientation available in versions of SQL prior to SQL-92. It moves the cursor from wherever it is to the next row in the set specified by the query expression. If the cursor is located before the first record, it moves to the first record. If it points to record n, it moves to record n+1. If the cursor points to the last record in the set, it moves beyond that record, and notification of a no data condition is returned in the SQLSTATE system variable. (Book IV, Chapter 4 details SQLSTATE and the rest of SQL’s error-handling facilities.)

The target specifications are either host variables or parameters, respectively, depending on whether embedded SQL or module language is using the cursor. The number and types of the target specifications must match the number and types of the columns specified by the query expression in the DECLARE CURSOR statement. So in the case of embedded SQL, when you fetch a list of five values from a row of

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.